package com.tj.shopping.commons;
import com.mysql.cj.jdbc.Driver;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 数据库操作的帮助类
 * 
 * @author 三石
 *
 */
public class DbHelper {
	private Connection conn;// 连接对象
	private PreparedStatement stmt;// 预编译对象
	private ResultSet rs;// 结果集对象

	// 驱动注册
	static {

		try {
			Driver driver = new Driver();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}

	}

	/**
	 * 获取连接对象
	 * 
	 * @return
	 */
	public Connection getConn() {
		try {
//			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "a");
			conn = DriverManager.getConnection(MyProperties.getInstance().getProperty("url"),MyProperties.getInstance());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 释放所有资源
	 * 
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public void closeAll(Connection conn, Statement stmt, ResultSet rs) {
		// 释放资源
		// 关闭结果集对象
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 关闭语句对象
		if (null != stmt) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 关闭连接对象
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 更新操作，多个sql语句    一个事务
	 * @param sqls
	 * @param params
	 * @return
	 */
	public int update(List<String>sqls,List<List<Object>> params) {
		int result =0;
		try {
			conn=getConn();
			conn.setAutoCommit(false);
			//循环sql语句
			for(int i=0;i<sqls.size();i++) {
				String sql = sqls.get(i);
				//获取当前sql语句对应的参数集合
				List<Object> list = params.get(i);
				//获取预编译对象
				stmt = conn.prepareStatement(sql);
				//设置参数
				setParams(stmt, list.toArray());
				//执行更新操作
				result = stmt.executeUpdate();
				if(result<=0) {
					//执行失败  事务回滚
					conn.rollback();
					result=0;
				}
			}
			//事务提交
			conn.commit();
		}catch (SQLException e) {
			try {
				conn.rollback();
				result=0;
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			System.out.println("事务处理异常："+e.getMessage());
			e.printStackTrace();
		}finally {
			//conn.setAutoCommit(true);//还原事务提交状态
			closeAll(conn, stmt, rs);
		}
		return result;
	}
	
	
	
	
	/**
	 * 更新操作  delete  insert  update  单条sql语句
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql,Object...params) {
		int result =0;
		try {
			conn=getConn();
			stmt=conn.prepareStatement(sql);
			setParams(stmt, params);	
			result=stmt.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
			System.out.println("单条sql语句更新异常。。。。。。");
		}finally {
			closeAll(conn, stmt, null);
		}
		return result;
	}
	/**
	 * 查询 select * from tb_admin where id=?    返回单条结果
	 * @param sql
	 * @param params
	 * @return
	 */
	public Map<String, Object> select(String sql,Object...params){
		List<Map<String, Object>> list = finds(sql, params);
		if(null==list||list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}
	
	/**
	 * 查询操作 返回多条数据 select * from 表名
	 * 
	 * @param sql    查询sql语句
	 * @param params sql语句的占位符需要的参数值 传入参数值顺序必须和sql语句中占位符?顺序一致
	 * @return
	 */
	public List<Map<String, Object>> finds(String sql, Object... params) {
		List<Map<String, Object>> list = new ArrayList<>();
		Map<String, Object> map = null;
		try {
			conn = getConn();
			stmt = conn.prepareStatement(sql);
			// 设置参数 传入参数值顺序必须和sql语句中占位符?顺序一致
			setParams(stmt, params);
			rs=stmt.executeQuery();
			List<String> columnNames = getColumnNames(rs);
			
			while (rs.next()) {
				map = new HashMap<>();
				// 列名作为键 --》如何获得键
				// 循环所有列
				for (String name : columnNames) {
					Object obj = rs.getObject(name);
					if (null == obj) {
						continue;
					}
					String typeName=obj.getClass().getName();
//					if("oracle.sql.BLOB".equals(typeName)) {//说明是图片
//						//将图片数据保存到字节数组中
//						BLOB blob =(BLOB) obj;
//						InputStream in = blob.getBinaryStream();
//						byte[] bt =new byte[(int)blob.length()];
//						in.read(bt);
//						map.put(name, bt);
//					}else {
						map.put(name, rs.getObject(name));
//					}
				}
				list.add(map);
			}

		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("查询语句出错，DbHelper finds");
		} finally {
			this.closeAll(conn, stmt, rs);
		}
		return list;
	}

	/**
	 * 获取所有列名
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List<String> getColumnNames(ResultSet rs) throws SQLException {
		List<String> names = new ArrayList<>();
		ResultSetMetaData metaData = rs.getMetaData();
		int count = metaData.getColumnCount();// 获取总列数
		for (int i = 1; i <= count; i++) {
			String name = metaData.getColumnName(i);
			names.add(name.toLowerCase());
		}
		return names;

	}

	/**
	 * 设置参数
	 * 
	 * @param stmt   当前编译对象
	 * @param params 传入的参数
	 * @throws SQLException
	 */
	public void setParams(PreparedStatement stmt, Object... params) throws SQLException {
		// 设置参数 传入参数值顺序必须和sql语句中占位符?顺序一致
		if (params != null && params.length > 0) {
			// 循环数组
			for (int i = 0; i < params.length; i++) {
				stmt.setObject(i + 1, params[i]);// 第几个？ 占位符?从1 开始
			}
		}
	}
	
	public double getPolymer(String sql,Object...params) {
		double result=0;
		try {
			conn=getConn();
			stmt=conn.prepareStatement(sql);
			setParams(stmt, params);
			rs=stmt.executeQuery();
			if(rs.next()) {
				result = rs.getDouble(1);
			}
		} catch (Exception e) {
			System.out.println("聚合函数getPolymer异常："+e.getMessage());
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 查询返回单条语句
	 * @param <T>
	 * @param sql
	 * @param cls
	 * @param params
	 * @return
	 */
	public <T> T select(String sql,Class<T> cls,Object...params) {
		List<T> list = finds(sql, cls, params);
		if(null==list||list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}
	
	
	
	/**
	 * 
	 * 查询返回多条件记录的
	 * @param <T>
	 * @param sql sql语句
	 * @param cls  对象class实例
	 * @param params  参数
	 * @return
	 */
	public <T> List<T> finds(String sql,Class<T> cls,Object...params){
		List<T> list = new ArrayList<>();
		T t = null;
		//获取class实例对象的成员方法
		Method []methods = cls.getDeclaredMethods();
		try {
			conn=getConn();
			stmt=conn.prepareStatement(sql);
			setParams(stmt, params);
			rs=stmt.executeQuery();
			//获取所有的列名
			List<String>columnNames = getColumnNames(rs);
			while(rs.next()) {
				//通过反射调用无参构造方法
				Constructor<T> cs = cls.getConstructor();
				t=cs.newInstance();//调用无参构造方法创建对象
				//循环所有列
				for(String cname:columnNames) {
					//获取值
					Object obj = rs.getObject(cname);
					if(null==obj) {
						continue;
					}
					//循环所有方法 找到setXxx()
					for(Method m:methods) {
						String methodName = m.getName();//获取方法
						if(("set"+cname).equalsIgnoreCase(methodName)) {
							//获取当前方法的形参类型
							String typeName = m.getParameterTypes()[0].getSimpleName();
							if("int".equals(typeName)||"Integer".equals(typeName)) {
								m.invoke(t, rs.getInt(cname));
							}else if("long".equals(typeName)||"Long".equals(typeName)) {
								m.invoke(t, rs.getLong(cname));
							}else if("float".equals(typeName)||"Float".equals(typeName)) {
								m.invoke(t, rs.getFloat(cname));
							}else if("double".equals(typeName)||"Double".equals(typeName)) {
								m.invoke(t, rs.getDouble(cname));
							}else {
								m.invoke(t, rs.getString(cname));
							}
						}
					}
				}
				list.add(t);
				
			}
		} catch (Exception e) {
			System.out.println("执行封装到对象的查询语句发生异常"+e.getMessage());
			e.printStackTrace();
		}finally {
			closeAll(conn, stmt, rs);
		}
		return list;
	}
	
	
}
